/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import Helper.UserHelper;
import java.sql.*;
import java.util.ArrayList;

/**
 *
 * @author KIENLT
 */
public class UserDAO {
    
    public static ResultSet checkLogin(Connection cnn, UserHelper obj) throws Exception {
        String strSQL = "SELECT * FROM tblUsers WHERE Username=? and Password=?";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        stmt.setString(1, obj.getUserName());
        stmt.setString(2, obj.getPassword());
        return stmt.executeQuery();
    }
    
    public static int Insert(Connection cnn, UserHelper obj) throws Exception{
        String strSQL = "INSERT INTO tblUsers(Username, Password, Phone, Email, RID) VALUES(?,?,?,?,?)";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        stmt.setString(1, obj.getUserName());
        stmt.setString(2, obj.getPassword());
        stmt.setString(3, obj.getPhone());
        stmt.setString(4, obj.getEmail());
        stmt.setString(5, obj.getRoles());
        return stmt.executeUpdate();
    }
    
    public static int Update(Connection cnn, UserHelper obj) throws Exception{
        String strSQL = "UPDATE tblUsers SET Username=?, Password=?, Phone=?, Email=?, RID=? WHERE UID=?";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        stmt.setString(1, obj.getUserName());
        stmt.setString(2, obj.getPassword());
        stmt.setString(3, obj.getPhone());
        stmt.setString(4, obj.getEmail());
        stmt.setString(5, obj.getRoles());
        stmt.setString(6, obj.getId());
        return stmt.executeUpdate();
    }
    
    public static int Delete(Connection cnn, UserHelper obj) throws Exception{
        String strSQL = "DELETE FROM tblUsers WHERE UID = ?";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        stmt.setString(1, obj.getId());
        return stmt.executeUpdate();
    }
    
   public static ResultSet FindOne(Connection cnn, UserHelper obj) throws Exception {
        String strSQL = "SELECT * FROM tblUsers t1, tblRoles t2 WHERE t1.RID=t2.RID and UID = ?";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        stmt.setString(1, obj.getId());
        return stmt.executeQuery();
    }
    
    public static ArrayList<UserHelper> FindAll(Connection cnn) throws Exception{
        ArrayList<UserHelper> arrUserList = new ArrayList<UserHelper>();
        UserHelper userData = null;
        String strSQL = "SELECT * FROM tblUsers t1, tblRoles t2 where t1.RID=t2.RID";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        ResultSet rs = stmt.executeQuery();
        while(rs.next()){
            userData = new UserHelper();
            userData.setId(rs.getString("UID"));
            userData.setUserName(rs.getString("Username"));
            userData.setPhone(rs.getString("Phone"));
            userData.setEmail(rs.getString("Email"));
            userData.setRoles(rs.getString("RName"));
            arrUserList.add(userData);
        }
        return arrUserList;
    }
    
    public static boolean checkUserName(Connection cnn, UserHelper obj) throws Exception {
        boolean check = false;
        String strSQL = "SELECT * FROM tblUsers WHERE Username=?";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        stmt.setString(1, obj.getUserName());
        ResultSet rs = stmt.executeQuery();
        if(rs.next()){
            check = true;
        }
        return check;
    }
    
    public static ArrayList<UserHelper> Search(Connection cnn, UserHelper obj) throws Exception{
        ArrayList<UserHelper> arrUserList = new ArrayList<UserHelper>();
        String strSQL = "SELECT * FROM tblUsers t1, tblRoles t2 where t1.RID=t2.RID";
        if(!obj.getUserName().equals("")){
            strSQL += " and t1.Username like '%"+obj.getUserName()+"%'";
        }
        if(!obj.getEmail().equals("")){
            strSQL += " and t1.Email like '%"+obj.getEmail()+"%'";
        }
        if(!obj.getPhone().equals("")){
            strSQL += " and t1.Phone like '%"+obj.getPhone()+"%'";
        }
        if(!obj.getRoles().equals("")){
            strSQL += " and t1.RID = '"+obj.getRoles()+"'";
        }
        
        Statement stmt = cnn.createStatement();
        ResultSet rs = stmt.executeQuery(strSQL);
        while(rs.next()){
            obj = new UserHelper();
            obj.setId(rs.getString("UID"));
            obj.setUserName(rs.getString("Username"));
            obj.setPhone(rs.getString("Phone"));
            obj.setEmail(rs.getString("Email"));
            obj.setRoles(rs.getString("RName"));
            arrUserList.add(obj);
        }
        return arrUserList;
    }
}
